package my.dao.Impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.List;

import my.utils.DBUtils;
import my.bean.Cart;
import my.bean.Customer;
import my.bean.Goods;
import my.dao.ICartDao;

public class ICartDaoImpl implements ICartDao {

	private Connection conn;

	public ICartDaoImpl() {

	}

	public ICartDaoImpl(Connection conn) {
		this.conn = conn;
	}

	public void setConnection(Connection conn) {
		this.conn = conn;
	}

	@Override
	public int add(Cart cart) throws SQLException {
		PreparedStatement prestmt = null;
		int record = 0;

		try {
			String sql = "insert into cart values(cart_id.nextval,?,?,?,?)";
			prestmt = conn.prepareStatement(sql);
			prestmt.setInt(1, cart.getCid());
			prestmt.setInt(2, cart.getGid());
			prestmt.setTimestamp(3, new Timestamp(cart.getBuyTime().getTime()));
			prestmt.setInt(4, cart.getBuyCount());

			record = prestmt.executeUpdate();

			if (record > 0) {
				System.out.println("添加成功!");
			} else {
				System.out.println("添加失败,请重新添加!");
			}

			return record;

		} catch (SQLException e) {
			e.printStackTrace();
			throw e;
		} finally {
			DBUtils.close(null, prestmt, null);
		}
	}

	@Override
	public int deleteById(int cid) throws SQLException {
		PreparedStatement prestmt = null;
		int record = 0;

		try {
			String sql = "delete from cart where cart_id=?";
			prestmt = conn.prepareStatement(sql);
			prestmt.setInt(1, cid);

			record = prestmt.executeUpdate();

			if (record > 0) {
				System.out.println("删除成功!");
			} else {
				System.out.println("删除失败,请重新操作!");
			}

			return record;
		} catch (SQLException e) {
			e.printStackTrace();
			throw e;
		} finally {
			DBUtils.close(null, prestmt, null);
		}
	}

	@Override
	public int modify(Cart cart) throws SQLException {
		PreparedStatement prestmt = null;
		int record = 0;

		try {
			String sql = "update cart set customer_id=?,goods_id=?,buy_count=? where cart_id=?";
			prestmt = conn.prepareStatement(sql);
			prestmt.setInt(1, cart.getCid());
			prestmt.setInt(2, cart.getGid());
			prestmt.setInt(3, cart.getBuyCount());
			prestmt.setInt(4, cart.getId());

			record = prestmt.executeUpdate();

			if (record > 0) {
				System.out.println("修改成功!");
			} else {
				System.out.println("修改失败,请重新操作!");
			}

			return record;
		} catch (SQLException e) {
			e.printStackTrace();
			throw e;
		} finally {
			DBUtils.close(null, prestmt, null);
		}
	}

	@Override
	public List<Cart> findAll() {
		PreparedStatement prestmt = null;
		List<Cart> list = new ArrayList<>();
		ResultSet rs = null;
		try {
			String sql = "select * from customer join cart on user_id=customer_id join goods on goods_id=gid";
			prestmt = conn.prepareStatement(sql);
			rs = prestmt.executeQuery();

			while (rs.next()) {
				int id = rs.getInt("CART_ID");
				int cid = rs.getInt("USER_ID");
				int gid = rs.getInt("GID");
				Timestamp time = rs.getTimestamp("BUY_TIME");
				int count = rs.getInt("BUY_COUNT");

				String account = rs.getString("user_account");
				String email = rs.getString("user_email");
				String phone = rs.getString("user_phone");
				Timestamp regDate = rs.getTimestamp("register_date");
				Customer customer = new Customer(cid, account, null, email, phone, regDate);

				String name = rs.getString("goods_name");
				double price = rs.getDouble("goods_price");
				int type = rs.getInt("goods_type");
				Goods good = new Goods(gid, name, price, type);

				Cart cart = new Cart(id, customer, good, time, count);
				list.add(cart);
			}

			return list;

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtils.close(null, prestmt, rs);
		}
		return list;
	}

	@Override
	public List<Cart> findByUserId(int userid) {
		PreparedStatement prestmt = null;
		List<Cart> list = new ArrayList<>();
		ResultSet rs = null;
		try {
			String sql = "select * from customer join cart on user_id=customer_id join goods on goods_id=gid where customer_id=?";
			prestmt = conn.prepareStatement(sql);
			prestmt.setInt(1, userid);
			rs = prestmt.executeQuery();

			while (rs.next()) {
				int id = rs.getInt("CART_ID");
				int cid = rs.getInt("USER_ID");
				int gid = rs.getInt("GID");
				Timestamp time = rs.getTimestamp("BUY_TIME");
				int count = rs.getInt("BUY_COUNT");

				String account = rs.getString("user_account");
				String email = rs.getString("user_email");
				String phone = rs.getString("user_phone");
				Timestamp regDate = rs.getTimestamp("register_date");
				Customer customer = new Customer(cid, account, null, email, phone, regDate);

				String name = rs.getString("goods_name");
				double price = rs.getDouble("goods_price");
				int type = rs.getInt("goods_type");
				Goods good = new Goods(gid, name, price, type);

				Cart cart = new Cart(id, customer, good, time, count);
				list.add(cart);
			}

			return list;

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtils.close(null, prestmt, rs);
		}
		return list;
	}

	@Override
	public Cart findByCartId(int cartId) {
		PreparedStatement prestmt = null;
		Cart cart = null;
		ResultSet rs = null;
		try {
			String sql = "select * from customer join cart on user_id=customer_id join goods on goods_id=gid where cart_id=?";
			prestmt = conn.prepareStatement(sql);
			rs = prestmt.executeQuery();

			while (rs.next()) {
				int id = rs.getInt("CART_ID");
				int cid = rs.getInt("USER_ID");
				int gid = rs.getInt("GID");
				Timestamp time = rs.getTimestamp("BUY_TIME");
				int count = rs.getInt("BUY_COUNT");

				String account = rs.getString("user_account");
				String email = rs.getString("user_email");
				String phone = rs.getString("user_phone");
				Timestamp regDate = rs.getTimestamp("register_date");
				Customer customer = new Customer(cid, account, null, email, phone, regDate);

				String name = rs.getString("goods_name");
				double price = rs.getDouble("goods_price");
				int type = rs.getInt("goods_type");
				Goods good = new Goods(gid, name, price, type);

				cart = new Cart(id, customer, good, time, count);
			}

			return cart;

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtils.close(null, prestmt, rs);
		}
		return cart;
	}

}
